﻿CREATE PROCEDURE [dbo].[proc_Finance_RunningAccountCopy_GetList_AccountsTypeId]
(
		@CompanyId int,
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@PaymentTypeId int,
		@BankId int,
		@Stext nvarchar(50),
		@AccountsTypeId int,
		@StartIndex int,
		@EndIndex int
	)
AS
begin
declare @strWhere NVARCHAR(4000)
set @strWhere='R.AccountsTypeId='+Convert(varchar,@AccountsTypeId)+' and '
if @CompanyId<>0
begin
	set @strWhere=@strWhere+'R.CompanyId='+Convert(varchar,@CompanyId)+' and '
end
if @PaymentTypeId<>0
begin
	set @strWhere=@strWhere+'R.PaymentTypeId='+Convert(varchar,@PaymentTypeId)+' and '
end
if @BankId<>0
begin
	set @strWhere=@strWhere+'R.BankId='+Convert(varchar,@BankId)+' and '
end
if @Stext<>''
begin
	set @strWhere=@strWhere+'R.Summary  like ''%'+ @Stext +'%'' and '
end

set @strWhere='

	Declare @sRq Datetime,@eRq Datetime,@eRq_s Datetime
	Set @sRq = Convert(Datetime, '''+@StartDate + ' 00:00:00'')
	Set @eRq = Convert(Datetime, '''+@EndDate + ' 23:59:59'')

	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY CreateDate desc)AS Row,
		R.Id,
		R.RId,
		R.CreateDate,
		R.TradeDate,
		R.Description,
		R.Summary,
		R.AccountsTypeId,
		R.AccountsTypeName,
		R.PaymentTypeId,
		R.PaymentTypeName,
		R.BankId,
		R.BankName,
		R.Income,
		R.Outlay,
		R.UserName,
		R.EName,
		R.BillNumber,
		(Select CompanyName From Company Where Id=R.CompanyId) As CompanyName
	From Finance_RunningAccountCopy R 
	Where   
	'+@strWhere+'
	  R.TradeDate between @sRq and @eRq
	
	)
	
	Select *,(Select Count(Id) From list) AS cid,
	(Select IsNULL(Sum(Case '+convert(varchar,@AccountsTypeId)+' When 1 Then Income Else Outlay End),0) From list) AS tsum 
	From list Where Row between '+ Convert(varchar, @StartIndex)+' and '+Convert(varchar,@EndIndex)+'
	'
	
	exec (@strWhere)
	
	
end
	RETURN
